USE [Widget]
GO
/****** Object: StoredProcedure [dbo].[sp_insert_order] Script Date: 8/23/2017 6:41:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ryan Parish
-- Create date: 4/4/17
-- Description: Insert a order in the main order table (tab_orders)
-- =============================================
ALTER PROCEDURE [dbo].[sp_insert_order]
@ORDER_ID integer, @EMAIL_ADDRESS char(100),
@CC_NUM varchar(16),
@DATE varchar(20) = NULL,
@MESSAGE varchar(50)= '' OUTPUT
AS
--Note that @DATE has a default value of NULL.
--You can set default values in your insert statement or your table.
--A good use of IF statements is to handle default values.
IF COALESCE(@ORDER_ID,0) = 0 BEGIN
SELECT
@ORDER_ID = MAX(id) + 1
FROM
tab_orders;
END
IF (SELECT COUNT(id)
FROM tab_orders
WHERE id=@ORDER_ID) > 0 BEGIN
SELECT @MESSAGE = 'Order ID already exists! '
END
--basic error checking
ELSE IF ISNUMERIC(@CC_NUM) =0 BEGIN
SELECT @MESSAGE = 'NOT a Credit Card Number'
END
ELSE IF CAST(@CC_NUM AS bigint) > 9999999999999999 OR CAST(@CC_NUM AS bigint) <= 999999999999999 BEGIN
SELECT @MESSAGE = 'Bad Credit Card Number'
END
--basic email
ELSE IF @EMAIL_ADDRESS NOT LIKE '%@%.%' AND len(@EMAIL_ADDRESS)>10 BEGIN
SELECT @MESSAGE = 'Bad Email'
END
ELSE IF @EMAIL_ADDRESS LIKE '%@aol.com%' BEGIN
SELECT @MESSAGE = 'Stupid Email'
END
ELSE IF ISDATE(@DATE) = 0 or NOT @DATE LIKE '%/%/%' BEGIN
SELECT @MESSAGE = 'Invalid Date format'
END
ELSE BEGIN
--Now that default values are set, do the insert
INSERT INTO tab_orders(
id,
email,
credit_card,
order_date
)
VALUES (
@ORDER_ID,
@EMAIL_ADDRESS,
@CC_NUM,
COALESCE(@DATE,GETDATE())
);
SELECT @MESSAGE = 'Order Information entered successfully!'
END
SELECT @MESSAGE AS [MESSAGE]